/**
 * Applikationsutveckling i Java, 5DV135
 * HT12
 * Project
 *
 * File: nullInvader.Engine.DatabaseHandler.java
 * Description: A class for comunicating with a database with highscore.
 * Date: 13 december 2012
 *
 * cs-user: dv11rlm
 * @author Robert Lundström
 */
package nullInvader.Engine;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class DatabaseHandler {

    final protected static String driver = "org.hsqldb.jdbcDriver";
    protected String url = "jdbc:hsqldb:hsql://";
    protected ArrayList<String> dataBaseResult;
    private Connection dbConnection = null;
    private String userName = "SA";
    private String userPassword = "";

    /**
     * loads the drivers needed for methods used later.
     *
     * @throws ClassNotFoundException
     */
    public DatabaseHandler() throws ClassNotFoundException {
    try {
        Class.forName(driver);
        url += "78.72.134.216:9001";
    } catch (ClassNotFoundException e) {
        // couldn't load driver.
        throw new ClassNotFoundException("unable to load "
            + driver);
    }
    dataBaseResult = new ArrayList<String>();
    }

    /**
     * loads the drivers needed for methods used later.
     *
     * @throws ClassNotFoundException
     */
    public DatabaseHandler(String serverAddress)
        throws ClassNotFoundException {
    try {
        Class.forName(driver);
        url += serverAddress;
    } catch (ClassNotFoundException e) {
        // couldn't load driver.
        throw new ClassNotFoundException("unable to load "
            + driver);
    }
    dataBaseResult = new ArrayList<String>();
    }

    /**
     * returns if it's possible to connect to the server.
     *
     * @return boolean
     */
    public boolean getConnectionStatus() {
    connectToDataBase();
    if (dbConnection == null) {
        return false;
    } else
        try {
        return !dbConnection.isClosed();
        } catch (SQLException e) {
        return false;
        }
    }

    /**
     * if it's possible to disconnect returns true.
     *
     * @return boolean.
     */
    private boolean disconnectDataBase() {
    try {
        if (dbConnection != null) {
        dbConnection.close();
        }
        return true;
    } catch (SQLException e) {
        return false;
    }
    }

    /**
     * connect to database using previously given values.
     *
     * @return boolean - returns if the method was successful.
     */
    protected boolean connectToDataBase() {
    try {
        DriverManager.setLoginTimeout(3);
        dbConnection = DriverManager.getConnection(url, // filenames
            userName, // username
            userPassword); // password
        return true;
    } catch (SQLException e) {
        return false;
    }
    }

    /**
     *
     * @param mapName
     * @return array of the person that have the highest score on the
     *         current map null if no highscore was found.
     */
    public String[] getHighscore(String mapName) {

    if (connectToDataBase()) {
        fillDatabaseList();
        String previousData = "";
        int highscore = 0;
        String highscoreName = null;
        for (int i = 0; i < dataBaseResult.size(); i++) {
        previousData += dataBaseResult.get(i) + " ";
        String[] temp = previousData.split(" ");
        if (temp.length == 3) {
            if (temp[1].equals(mapName)) {
            int result = Integer.parseInt(temp[2]);
            if (result > highscore) {
                highscoreName = temp[0];
                highscore = result;
            }
            }
            previousData = "";
        }
        }
        disconnectDataBase();
        return (highscoreName + " " + highscore).split(" ");
    }
    disconnectDataBase();
    return null;
    }

    /**
     * send the values to the database.
     *
     *
     * @param nickname
     *            - maxLength 100
     * @param mapName
     *            - maxLength 256
     * @param score
     * @throws SQLException
     *
     * @see java.sql.Statement#executeUpdate(String sql)
     */
    public void submitScore(String nickname, String mapName, int score)
        throws SQLException {
    if (!connectToDataBase()) {
        return;
    }
    createTableIfNotExist();
    if (nickname.length() > 100) {
        Statement statement = dbConnection.createStatement();
        statement.executeUpdate("INSERT INTO "
            + "PUBLIC.HIGHSCORE(USER_NAME,"
            + "USER_MAPNAME, USER_SCORE) VALUES ('"
            + nickname.subSequence(0, 99) + "','" + mapName
            + "',"
            + score + ")");
    }
    Statement statement = dbConnection.createStatement();
    statement.executeUpdate("INSERT INTO "
        + "PUBLIC.HIGHSCORE(USER_NAME,"
        + "USER_MAPNAME, USER_SCORE) VALUES ('" + nickname
        + "','"
        + mapName + "'," + score + ")");
    disconnectDataBase();
    }

    /**
     * Fill dataBaseResult with latest information and then return it.
     *
     * @see nullInvader.Engine.DatabaseHandler#dataBaseResult
     * @see nullInvader.Engine.DatabaseHandler#fillDatabaseList()
     * @return ArrayList<String> - dataBaseResult
     */
    public ArrayList<String> getDataBaseResult() {
    fillDatabaseList();
    return dataBaseResult;
    }

    /**
     * will remove all previously given scores in the database.
     *
     * @see java.sql.Statement#executeUpdate(String sql)
     * @throws SQLException
     */
    public void dropHighscoreList() throws SQLException {
    if (!connectToDataBase()) {
        return;
    }
    Statement statement = dbConnection.createStatement();

    statement.executeUpdate("DELETE FROM PUBLIC.HIGHSCORE");
    disconnectDataBase();
    }

    /**
     * Connect to the database and get all data from the table
     * PUBLIC.HIGHSCORE.
     *
     * @see java.sql.Statement#executeQuery(String sql)
     */
    public void fillDatabaseList() {
    if (connectToDataBase()) {
        dataBaseResult = new ArrayList<String>();
        try {
        createTableIfNotExist();
        Statement stm1 = dbConnection.createStatement();
        ResultSet rs = stm1
            .executeQuery("SELECT * FROM PUBLIC.HIGHSCORE");
        ResultSetMetaData meta = rs.getMetaData();
        int colmax = meta.getColumnCount();
        int i;
        Object o = null;
        for (; rs.next();) {
            for (i = 0; i < colmax; ++i) {
            o = rs.getObject(i + 1); // Is SQL the first
                         // column is
                         // indexed
            dataBaseResult.add(o.toString().replaceAll(
                " ", "_"));
            }
        }
        } catch (SQLException e) {
        /*
         * Broken pipe, connection might've broken during
         * after connection got opened. do nothing,
         * getDatabaseResult might return null.
         */

        return;
        }

        disconnectDataBase();
    }
    }

    /**
     * Tries to create a table if one dosn't exist sice earlier then
     * returns either(1) the row count for SQL Data Manitpulator
     * Language or (2) 0 for SQL statements that returns nothing
     *
     * @see java.sql.Statement.executeUpdate(String sql)
     * @return int
     * @throws SQLException
     */
    private int createTableIfNotExist() throws SQLException {
    Statement statement = dbConnection.createStatement();
    return statement.executeUpdate("CREATE TABLE IF NOT EXISTS "
        + "HIGHSCORE(USER_NAME VARCHAR(100), "
        + "USER_MAPNAME VARCHAR(256), USER_SCORE INTEGER)");
    }

    /**
     * Set userName to specified userName.
     *
     * @param userName
     */
    public void setUserName(String userName) {
    this.userName = userName;
    }

    /**
     * Set userPassword to specified userPassword.
     *
     * @param userPassword
     */
    public void setUserPassword(String userPassword) {
    this.userPassword = userPassword;
    }
}
